/**
 * @ClassName: UserServiceDao
 * @author linsen
 * @date 2016-10-12
 */
package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.core.tool.CObjectTool;
import com.fzrskj.cloud.free.core.utils.AgentDataRangeUtils;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.core.utils.DataRangeUtils;
import com.fzrskj.cloud.free.user.bean.statistics.Statistics;
import com.fzrskj.cloud.free.user.tool.StatistisTool;
import com.fzrskj.cloud.free.user.bean.UserService;
import com.fzrskj.cloud.free.user.bean.UserServiceView;
import com.winiis.component.common.utils.db.DbUtils;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;
import org.apache.commons.collections.MapUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class UserServiceDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserServiceDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        db.closeConnection();
    }

    //to add new record to database by bean
    public int addUserService(UserService obj) {
        db.startTran();
        if (obj.getAgentId() == null) {
            obj.setAgentId(CloudContextUtils.getAgentId());
        }
        String sql = "insert into user_service(id,applyTime,endTime,hostType,instanceId,instanceName,publicIP," +
                "privateIP,orderId,price,priceType,productCode,regionId," +
                "regionName,zoneId,zoneName,serviceId,startTime,status," +
                "userName,userId,agentId)" +
                "values(?,?,?,?,?,?,?," +
                "?,?,?,?,?,?," +
                "?,?,?,?,?,?," +
                "?,?,?);";
        Object[] prm = {obj.getId(), obj.getApplyTime(), obj.getEndTime(), obj.getHostType(), obj.getInstanceId(), obj.getInstanceName(), obj.getPublicIP(),
                obj.getPrivateIP(), obj.getOrderId(), obj.getPrice(), obj.getPriceType(), obj.getProductCode(), obj.getRegionId(),
                obj.getRegionName(), obj.getZoneId(), obj.getZoneName(), obj.getServiceId(), obj.getStartTime(), obj.getStatus(),
                obj.getUserName(), obj.getUserId(), obj.getAgentId()};
        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (ret > 0) {
            ret = DbUtils.lastId(this.db);
        }
        db.commitTran();
        return ret;
    }

    //to add new record to database by map
    public int addUserService(Map<String, Object> values) {
        //if (!values.containsKey("agentId")) {
        values.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.insertRecord("user_service", values);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by id
    public int delUserService(int id) {
        Object[] params = {id};
        String sql = "delete from user_service where id=?";

        sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        int ret = db.executeSql(sql, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by cdt
    public int delUserService(Map<String, Object> cdt) {
        StringBuilder sb = new StringBuilder("delete from user_service where 1=1 ");
        List<Object> list = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            sb.append(" and " + c.getKey() + "=?");
            list.add(c.getValue());
        }

        sb = AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sb);

        int ret = db.executeSql(sb.toString(), list);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to modify table record by values and condition
    public int modUserService(Map<String, Object> values, Map<String, Object> cdt) {
        if (!cdt.containsKey("agentId")) {
            cdt.put("agentId", CloudContextUtils.getAgentId());
        }
        int ret = db.modifyRecord("user_service", values, cdt);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    //to query UserService record count
    public int queryUserService(Map<String, Object> cdt) {
        cdt.put("agentId", CloudContextUtils.getAgentId());
        int ret = db.queryRecordCount("user_service", cdt);
        this.setErrCode(db.getErrCode());
        return ret;
    }

    /**
     * 查询云主机列表
     *
     * @param cdt
     * @param page
     * @param pagesize
     * @return
     */
    public List<UserServiceView> queryUserServiceList(Map<String, Object> cdt, int page, int pagesize) {
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select us.*,");
        sb.append("(select productName from sys_product sp where sp.productCode=us.productCode");

        //代理商数据隔离
        sb.append(" and sp.agentId=us.agentId ");

        sb.append(") as productName,");
        sb.append("(select statusName from dic_datastatus dd where dd.hostType=us.hostType and dd.status=us.status and statusType='instance') as statusName ");
        sb.append(", (select aloneIp from user_vps vps where us.instanceId=vps.vpsId limit 1) as aloneIp ");
        sb.append(", (select paramValue from dic_param dpc where dpc.agentId=us.agentId and dpc.paramName='webname' limit 1) as platName ");
        sb.append(" from user_service us where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sb.append(" and endTime >?");
            } else if (c.getKey().equals("end")) {
                sb.append(" and endTime <=?");
            } else if (c.getKey().equals("startFrom")) {
                sb.append(" and startTime >?");
            } else if (c.getKey().equals("startLimit")) {
                sb.append(" and startTime <=?");
            } else{
                sb.append(" and " + c.getKey() + "=?");
            }
            list.add(c.getValue());
        }

        //管理数据隔离
        DataRangeUtils.formatRangeSql(sb, " us.userId ", list);
        //代理商数据隔离
        sb = AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sb, "us.agentId");

        sb.append(" order by us.endTime asc");
        if ((page > 0) && (pagesize > 0)) {
            sb.append(String.format(" limit %d,%d", (page - 1) * pagesize, pagesize));
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserServiceView> ret = db.queryForBeans(sb.toString(), UserServiceView.class, params);
        this.setErrCode(db.getErrCode());
        return ret;
    }

    /**
     * 统计主机数
     *
     * @param cdt
     * @return
     */
    public int queryUserServiceCount(Map<String, Object> cdt) {
        //cdt.put("agentId", CloudContextUtils.getAgentId());
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select count(id) from user_service where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sb.append(" and endTime >?");
            } else if (c.getKey().equals("end")) {
                sb.append(" and endTime <=?");
            } else if (c.getKey().equals("startFrom")) {
                sb.append(" and startTime >?");
            } else if (c.getKey().equals("startLimit")) {
                sb.append(" and startTime <=?");
            } else{
                sb.append(" and " + c.getKey() + "=?");
            }
            list.add(c.getValue());
        }

        //数据隔离
        DataRangeUtils.formatRangeSql(sb, " userId ", list);
        AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sb);

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long ret = (Long) db.queryFieldValue(sb.toString(), Long.class, params, 1);
        this.setErrCode(db.getErrCode());
        return ret.intValue();
    }

    public UserServiceView queryUsViewByIid(int userId, String instanceId) {
        return this.queryUsViewByIidAppointAgent(userId, instanceId, CloudContextUtils.getAgentId());
    }

    /**
     * 获取云服务器业务详情
     *
     * @param userId
     * @param instanceId
     * @return
     */
    public UserServiceView queryUsViewByIidAppointAgent(int userId, String instanceId, Integer agentId) {
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select us.*,");
        sb.append("(select productName from sys_product sp where sp.productCode=us.productCode");

        //代理商数据隔离
        sb.append(" and sp.agentId=us.agentId ) ");

        sb.append("as productName,");
        sb.append("IFnull(regionName, (select regionName from dic_hostlocation dh where dh.regionId=us.regionId)) as regionName,");
        sb.append("(select statusName from dic_datastatus dd where dd.hostType=us.hostType and dd.status=us.status and statusType='instance') as statusName ");
        sb.append("from user_service us where 1=1 and us.instanceId=? ");
        list.add(instanceId);
        if (userId > 0) {
            sb.append(" and us.userId=? ");
            list.add(userId);
        }

        //代理商数据隔离
        //sb.append(" and us.agentId=? ");
        //list.add(agentId);
        sb = AgentDataRangeUtils.formatDataRange(agentId, sb, "us.agentId");

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        UserServiceView ret = db.queryForBean(sb.toString(), UserServiceView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }


    public UserServiceView queryUsViewByIidWithoutAgent(int userId, String instanceId) {
        List<Object> list = new ArrayList<>();
        StringBuffer sb = new StringBuffer("select us.*,");
        sb.append("(select productName from sys_product sp where sp.productCode=us.productCode and sp.agentId=us.agentId)");

        sb.append("as productName,");
        sb.append("IFnull(regionName, (select regionName from dic_hostlocation dh where dh.regionId=us.regionId)) as regionName,");
        sb.append("(select statusName from dic_datastatus dd where dd.hostType=us.hostType and dd.status=us.status and statusType='instance') as statusName ");
        sb.append("from user_service us where 1=1 and us.instanceId=? ");
        list.add(instanceId);
        if (userId > 0) {
            sb.append(" and us.userId=? ");
            list.add(userId);
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        UserServiceView ret = db.queryForBean(sb.toString(), UserServiceView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 获取云服务器业务详情
     *
     * @param userId
     * @param serviceId
     * @return
     */
    public UserServiceView queryUsViewBySid(int userId, String serviceId) {
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select us.*,");
        sb.append("(select productName from sys_product sp where sp.productCode=us.productCode");

        //代理商数据隔离
        sb.append(" and sp.agentId=us.agentId ");
        //list.add(CloudContextUtils.getAgentId());

        sb.append(") as productName,");
        sb.append("(select regionName from dic_hostlocation dh where dh.regionId=us.regionId and dh.hostType='cloud') as regionName,");
        sb.append("(select statusName from dic_datastatus dd where dd.hostType=us.hostType and dd.status=us.status and statusType='instance') as statusName ");
        sb.append("from user_service us where 1=1 and us.serviceId=? ");
        list.add(serviceId);
        if (userId > 0) {
            sb.append("and us.userId=?");
            list.add(userId);
        }

        //代理商数据隔离
        //sb.append(" and us.agentId=? ");
        //list.add(CloudContextUtils.getAgentId());
        sb = AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sb, "us.agentId");

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        UserServiceView ret = db.queryForBean(sb.toString(), UserServiceView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    public UserService queryUsByIid(int userId, String instanceId) {
        return this.queryUsByIidAppointAgent(userId, instanceId, CloudContextUtils.getAgentId());
    }

    /**
     * 获取云服务器业务
     *
     * @param userId
     * @param instanceId
     * @return
     */
    public UserService queryUsByIidAppointAgent(int userId, String instanceId, Integer agentId) {
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select us.* from user_service us where 1=1 and us.instanceId=? ");
        list.add(instanceId);
        if (userId > 0) {
            sb.append("and us.userId=?");
            list.add(userId);
        }

        //代理商数据隔离
        //sb.append(" and us.agentId=? ");
        //list.add(agentId);
        sb = AgentDataRangeUtils.formatDataRange(agentId, sb, "us.agentId");

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        UserService ret = db.queryForBean(sb.toString(), UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询将要到期的云主机
     *
     * @param startTime
     * @param endTime
     * @return
     */
    public List<UserService> queryExpiryUserService(String startTime, String endTime) {
        String sql = "select * from user_service where endTime>=? and endTime<?";
        Object[] params = {startTime, endTime};

        //代理商数据隔离
        sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        List<UserService> ret = db.queryForBeans(sql, UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询现在到期的云主机
     *
     * @param endTime
     * @return
     */
    public List<UserService> queryNowExpiryUs(String endTime) {
        String sql = "select * from user_service where Date(endTime)<=Date(?) and status!='Expire'";
        Object[] params = {endTime};

        //代理商数据隔离
        sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        List<UserService> ret = db.queryForBeans(sql, UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询已经到期的云主机
     *
     * @param endTime
     * @return
     */
    public List<UserService> queryAlreadyExpiryUs(String endTime) {
        String sql = "select * from user_service where Date(endTime)<=Date(?) and status='Expire'";
        Object[] params = {endTime};

        //代理商数据隔离
        sql += " and agentId=? ";
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        List<UserService> ret = db.queryForBeans(sql, UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to query user_service item for ExpiryServiceCount
    public int queryExpiryServiceCount(Map<String, Object> cdt) {
        List<Object> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select  ifnull(count(id),0) from user_service where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and endTime >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and endTime <?");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }
        //代理商数据隔离
        AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sql);

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long total = (Long) db.queryFieldValue(sql.toString(), Long.class, params, 1);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (total < 0 || total == null)
            return 0;
        return total.intValue();
    }

    public int queryNewServiceCount(Map<String, Object> cdt) {

        //代理商数据隔离
        //cdt.put("agentId", CloudContextUtils.getAgentId());

        List<Object> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select  ifnull(count(id),0) from user_service where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and startTime >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and startTime <?");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        //代理隔离
        AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sql);

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long total = (Long) db.queryFieldValue(sql.toString(), Long.class, params, 1);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (total < 0 || total == null)
            return 0;
        return total.intValue();
    }

    /**
     * 根据IP查询服务器
     *
     * @param userId
     * @param publicIP
     * @return
     */
    public UserService queryUsByIP(int userId, String publicIP) {
        StringBuilder sb = new StringBuilder("select * from user_service where userId=? and publicIP=?");
        Object[] params = {userId, publicIP};

        //代理商数据隔离
        sb.append(" and agentId=? ");
        params = CObjectTool.objectArrAddValue(params, CloudContextUtils.getAgentId());

        UserService us = db.queryForBean(sb.toString(), UserService.class, params);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(code);
        }
        return us;
    }

    /**
     * 查询云主机列表
     *
     * @param cdt
     * @return
     */
    public List<UserService> queryUserServiceList(Map<String, Object> cdt) {
        //代理商数据隔离
        Integer agentId = CloudContextUtils.getAgentId();
        if (cdt.containsKey("agentId")) {
            agentId = MapUtils.getInteger(cdt, "agentId");
            cdt.remove("agentId");
        }
        StringBuilder sb = new StringBuilder("select * from user_service where 1=1 ");
        List<Object> list = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            sb.append(" and " + c.getKey() + "=?");
            list.add(c.getValue());
        }

        sb = AgentDataRangeUtils.formatDataRange(agentId, sb);

        sb.append(" order by startTime asc");
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserService> ret = db.queryForBeans(sb.toString(), UserService.class, params);
        this.setErrCode(db.getErrCode());
        return ret;
    }

    /**
     * 按时间段统计符合条件新开实例
     *
     * @param cdt
     * @param qryType H 小时 D 天 M 月 Y 年
     * @return add wzj by 2018/12/12
     */
    public List<Statistics> queryServiceCount(Map<String, Object> cdt, String qryType) {
        //代理商数据隔离
        cdt.put("agentId", CloudContextUtils.getAgentId());

        String format = StatistisTool.getSqlDateFormat(qryType);
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select count(id) as count, DATE_FORMAT(startTime, '" + format + "') as dateTime from user_service where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("startTime")) {
                sb.append(" and startTime >= ?");
                list.add(c.getValue());
            } else if (c.getKey().equals("endTime")) {
                sb.append(" and startTime <= ?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        sb.append(" group by DATE_FORMAT(startTime,'" + format + "') ");
        sb.append(" order by startTime asc");
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Statistics> rets = db.queryForBeans(sb.toString(), Statistics.class, params);
        this.setErrCode(db.getErrCode());
        return rets;
    }

    /**
     * 获取所有未到期业务.
     *
     * @return List<UserServiceView>.
     */
    public List<UserServiceView> queryAllUserServiceNormalList() {

        List<Object> list = new ArrayList<>();
        StringBuffer sb = new StringBuffer("select us.*,");
        sb.append("(select productName from sys_product sp where sp.productCode=us.productCode");

        //代理商数据隔离
        sb.append(" and sp.agentId=? ");
        list.add(CloudContextUtils.getAgentId());

        sb.append(") as productName,");
        sb.append("(select statusName from dic_datastatus dd where dd.hostType=us.hostType and dd.status=us.status and statusType='instance') as statusName ");
        sb.append("from user_service us where 1=1 ");

        sb.append(" and us.endTime >?");
        list.add(new Date());

        //代理商数据隔离
        sb.append(" and us.agentId=? ");
        list.add(CloudContextUtils.getAgentId());

        sb.append(" order by us.endTime asc");
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserServiceView> ret = db.queryForBeans(sb.toString(), UserServiceView.class, params);
        this.setErrCode(db.getErrCode());
        return ret;
    }


    /*===================================================================自动任务专用========================================================================================*/

    /**
     * 查询现在到期的云主机(自动任务专用)
     *
     * @param endTime
     * @return
     */
    public List<UserService> queryTaskNowExpiryUs(String endTime) {
        String sql = "select * from user_service where Date(endTime)<=Date(?) and status!='Expire'";
        Object[] params = {endTime};

        List<UserService> ret = db.queryForBeans(sql, UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询将要到期的云主机(自动任务专用)
     *
     * @param startTime
     * @param endTime
     * @return
     */
    public List<UserService> queryTaskExpiryUserService(String startTime, String endTime) {
        String sql = "select * from user_service where endTime>=? and endTime<?";
        Object[] params = {startTime, endTime};

        List<UserService> ret = db.queryForBeans(sql, UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }



    /**
     * 查询已经到期的云主机(自动任务专用)
     *
     * @param endTime
     * @return
     */
    public List<UserService> queryTaskAlreadyExpiryUs(String endTime) {
        String sql = "select * from user_service where Date(endTime)<=Date(?) and status='Expire'";
        Object[] params = {endTime};

        List<UserService> ret = db.queryForBeans(sql, UserService.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }
}